********************************************************************************
global basepath ".../Data"
global results ".../Results"
*
cap n clear all
cap n clear matrix
cap n set max_memory 400g
cap n set maxvar 120000
cap n set segmentsize 1g
cap n set max_preservemem 100g
cap n set more off
cap n set checksum off
cap n set type double
cap n set processors 6
cap n version 16.1
********************************************************************************


********************************************************************************
** Tables 1, C1, and C2: Descriptive Statistics
********************************************************************************

clear all
use "$basepath/dataRWA_BalancedPanel.dta", clear
*
preserve
keep D_loan D_loan_SACCO D_loan_MFI D_loan_BANK
outreg2 using "$results/Table_1_PanelA.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_1_PanelA.txt"
restore
*
preserve
keep D_loan D_loan_SACCO D_loan_MFI D_loan_BANK
keep if D_loan_SACCO==1
outreg2 using "$results/Table_C1_SACCO_PanelA.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_SACCO_PanelA.txt"
restore
*
preserve
keep D_loan D_loan_SACCO D_loan_MFI D_loan_BANK
keep if D_loan_BANK==1
outreg2 using "$results/Table_C1_BANK_PanelA.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_BANK_PanelA.txt"
restore
*
preserve
keep D_loan D_loan_SACCO D_loan_MFI D_loan_BANK
keep if D_loan_MFI==1
outreg2 using "$results/Table_C1_MFI_PanelA.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_MFI_PanelA.txt"
restore

***

clear all
use "$basepath/dataRWA.dta", clear
*
preserve
drop if currentbalanceamount==. | principalamount==. | maturity==. | npl==.
keep currentbalanceamount principalamount interestrate maturity npl
order currentbalanceamount principalamount interestrate maturity npl
outreg2 using "$results/Table_1_PanelB.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_1_PanelB.txt"
restore
*
preserve
keep if SACCO==1
drop if currentbalanceamount==. | principalamount==. | maturity==. | npl==.
keep currentbalanceamount principalamount interestrate maturity npl
order currentbalanceamount principalamount interestrate maturity npl
outreg2 using "$results/Table_C1_SACCO_PanelB.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_SACCO_PanelB.txt"
restore
*
preserve
keep if BANK==1
drop if currentbalanceamount==. | principalamount==. | maturity==. | npl==.
keep currentbalanceamount principalamount interestrate maturity npl
order currentbalanceamount principalamount interestrate maturity npl
outreg2 using "$results/Table_C1_BANK_PanelB.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_BANK_PanelB.txt"
restore
*
preserve
keep if MFI==1
drop if currentbalanceamount==. | principalamount==. | maturity==. | npl==.
keep currentbalanceamount principalamount interestrate maturity npl
order currentbalanceamount principalamount interestrate maturity npl
outreg2 using "$results/Table_C1_MFI_PanelB.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_MFI_PanelB.txt"
restore
*
gunique bank_id
gunique bank_id if SACCO==1
gunique bank_id if BANK==1
gunique bank_id if MFI==1

****

clear all
use "$basepath/dataRWA_BalancedPanel.dta", clear
*
preserve
gcollapse (mean) female single young gvt_employee, by(borrower_id)
drop borrower_id
outreg2 using "$results/Table_1_PanelC.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_1_PanelC.txt"
restore
*
preserve
keep if D_loan_SACCO==1
gcollapse (mean) female single young gvt_employee, by(borrower_id)
drop borrower_id
outreg2 using "$results/Table_C1_SACCO_PanelC.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_SACCO_PanelC.txt"
restore
*
preserve
keep if D_loan_BANK==1
gcollapse (mean) female single young gvt_employee, by(borrower_id)
drop borrower_id
outreg2 using "$results/Table_C1_BANK_PanelC.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_BANK_PanelC.txt"
restore
*
preserve
keep if D_loan_MFI==1
gcollapse (mean) female single young gvt_employee, by(borrower_id)
drop borrower_id
outreg2 using "$results/Table_C1_MFI_PanelC.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_MFI_PanelC.txt"
restore

***

clear all
use "$basepath/dataRWA.dta", clear
*
preserve
gcollapse (mean) bank_presence growth nlights_2010 poverty_index population_by_sector conflicts duration rural, by(sector_id)
drop sector_id
outreg2 using "$results/Table_1_PanelD.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_1_PanelD.txt"
restore
*
preserve
keep if SACCO==1
gcollapse (mean) bank_presence growth nlights_2010 poverty_index population_by_sector conflicts duration rural, by(sector_id)
drop sector_id
outreg2 using "$results/Table_C1_SACCO_PanelD.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_SACCO_PanelD.txt"
restore
*
preserve
keep if BANK==1
gcollapse (mean) bank_presence growth nlights_2010 poverty_index population_by_sector conflicts duration rural, by(sector_id)
drop sector_id
outreg2 using "$results/Table_C1_BANK_PanelD.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_BANK_PanelD.txt"
restore
*
preserve
keep if MFI==1
gcollapse (mean) bank_presence growth nlights_2010 poverty_index population_by_sector conflicts duration rural, by(sector_id)
drop sector_id
outreg2 using "$results/Table_C1_MFI_PanelD.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C1_MFI_PanelD.txt"
restore

***

clear all
use "$basepath/dataRWA.dta"
*
merge m:1 sector district using "$basepath/sacco_BS_2010.dta", keep(master match) nogen
gcollapse (mean) no_members_with_paid_shares_10 total_assets_10 deposits_10 capital_ratio_10 liquidity_ratio_10, by(sector_id)
drop sector_id
outreg2 using "$results/Table_C2.xls", replace sum(detail) eqkeep(N mean p50 sd) dec(3)
erase "$results/Table_C2.txt"


********************************************************************************
** Table 2: The Timing of the U-SACCO Program and Local Conditions
********************************************************************************

clear all
use "$basepath/dataRWA.dta", clear
*
gcollapse (mean) bank_presence growth nlights_2010 poverty_index population_by_sector conflicts duration rural, by(sector district sector_id)
merge 1:1 sector district using "$basepath/sacco_BS_2010.dta", keep(master match) nogen
merge 1:1 sector district using "$basepath/first_loan_SACCOs.dta", keep(master match) nogen
*
gen fail = 0
replace fail = 1 if first_loan_SACCOs!=.
replace first_loan_SACCOs = 1000 if first_loan_SACCOs==.
stset first_loan_SACCOs, id(sector_id) fail(fail)
stsum
global zvar "growth nlights_2010 poverty_index bank_presence no_members_with_paid_shares_10 total_assets_10 deposits_10 capital_ratio_10 liquidity_ratio_10 population_by_sector conflicts duration rural"
order $zvar
encode district, gen(d)
*
cap n erase "$results/Table_2.xls"
foreach x of varlist $zvar {
	stcox `x', vce(cluster sector) nohr
	qui outreg2 using "$results/Table_2.xls", keep($zvar) nocons bdec(3) tdec(3) se append 
}
stcox $zvar, vce(cluster sector) nohr
qui outreg2 using "$results/Table_2.xls", keep($zvar) nocons bdec(3) tdec(3) se append 
cap n erase "$results/Table_2.txt"


********************************************************************************
** Table C3: The Timing of the U-SACCO Program: Early versus Late Starters
********************************************************************************

clear all
use "$basepath/dataRWA.dta", clear
*
gcollapse (mean) bank_presence growth nlights_2010 poverty_index population_by_sector conflicts duration rural, by(sector district sector_id)
merge 1:1 sector district using "$basepath/sacco_BS_2010.dta", keep(master match) nogen
merge 1:1 sector district using "$basepath/first_loan_SACCOs.dta", keep(master match) nogen
*
gen late_starter=(first_loan_SACCOs>ym(2013,7))
sum late_starter
global controls growth nlights_2010 poverty_index bank_presence no_members_with_paid_shares_10 total_assets_10 deposits_10 capital_ratio_10 liquidity_ratio_10 population_by_sector conflicts duration rural
order late_starter $controls
keep late_starter $controls
*
gen vname = ""
foreach rhsv of varlist late_starter {
	gen `rhsv'0 = .
	gen `rhsv'1 = .
	gen `rhsv'_c = .
	gen `rhsv'_t = .
	gen `rhsv'_s = ""
}
*
local i=1
foreach var of global controls {
	replace vname = "`var'" in `i'
foreach rhsv of varlist late_starter {
	mean `var', over(`rhsv')
	replace `rhsv'0 = _b[c.`var'@0.`rhsv'] in `i'
	replace `rhsv'1 = _b[c.`var'@1.`rhsv'] in `i'
	replace `rhsv'_c = e(N) in `i'
	test _b[c.`var'@0.`rhsv']=_b[c.`var'@1.`rhsv']
	replace `rhsv'_t=r(p) in `i'
}
	local i=`i'+1
}
keep vname late_starter late_starter0 late_starter1 late_starter_c late_starter_t late_starter_s
forvalues j=1/`=_N'{
foreach rhsv of varlist late_starter {
	local pval=`rhsv'_t[`j']
if `pval' <=0.01 {
	replace `rhsv'_s="***" in `j'
}
else if `pval' <=0.05 {
	replace `rhsv'_s="**" in `j'
}
 
else if `pval' <=0.10 {
	replace `rhsv'_s="*" in `j'
}
}
} 
format late_starter_c %12.0fc 
format late_starter0 late_starter1 %12.4f
format late_starter_t %10.4f
keep vname late_starter_c late_starter0 late_starter1 late_starter_t late_starter_s
order vname late_starter_c late_starter0 late_starter1 late_starter_t late_starter_s
drop if late_starter_c==.
export excel using "$results/Table_C3.xls", replace firstrow(variables)

